# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.font_manager import FontProperties
import os
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

# 设置输出文件夹路径
output_folder = r'output/20'
os.makedirs(output_folder, exist_ok=True)

# 加载数据（请替换为实际文件路径）
file_path = r'data/20/女装店铺客户画像绘制-原始数据.xlsx'

# 读取客户信息表
df_customers = pd.read_excel(file_path)

# 设置中文字体
font_path = r'fonts/SIMSUN.TTC'  # 请根据实际路径调整
font_prop = FontProperties(fname=font_path)

def save_analysis_to_excel(pivot_table, output_image_path, output_excel_path, sheet_title, conclusion_text):
    with pd.ExcelWriter(output_excel_path, engine='openpyxl') as writer:
        pivot_table.to_excel(writer, sheet_name=sheet_title)
        
        workbook = writer.book
        worksheet = writer.sheets[sheet_title]
        
        img = Image(output_image_path)
        worksheet.add_image(img, 'E2')

        analysis_sheet = workbook.create_sheet(title='分析结论')
        analysis_sheet.cell(row=1, column=1, value=conclusion_text)

# 第一题：客户来源地域分析
pivot_table_region = pd.pivot_table(df_customers, values='客户昵称', index='客户来源地域', aggfunc='count')
pivot_table_region.columns = ['客户数量']

plt.figure(figsize=(14, 7))
pivot_table_region.plot(kind='bar', legend=False)
plt.xlabel('来源地域', fontproperties=font_prop)
plt.ylabel('客户数量', fontproperties=font_prop)
plt.title('客户来源地域分析', fontproperties=font_prop)
plt.xticks(fontproperties=font_prop, rotation=45)
plt.tight_layout()

output_image_path_region = os.path.join(output_folder, 'customer_region_distribution.png')
plt.savefig(output_image_path_region, dpi=300, bbox_inches='tight')
plt.close()

most_common_region = pivot_table_region['客户数量'].idxmax()
print(f"该网店客户来源最多的地域是 {most_common_region}。")

output_excel_path_region = os.path.join(output_folder, '客户来源地域分析.xlsx')
save_analysis_to_excel(
    pivot_table_region,
    output_image_path_region,
    output_excel_path_region,
    '客户来源地域分析',
    f"该网店客户来源最多的地域是 {most_common_region}。"
)

print("客户来源地域分析已完成，结果已保存到", output_excel_path_region)

# 第三题：客户性别分析
pivot_table_gender = pd.pivot_table(df_customers, values='客户昵称', index='客户性别', aggfunc='count')
pivot_table_gender.columns = ['客户数量']
gender_percentage = (pivot_table_gender / pivot_table_gender.sum() * 100).round(2)

plt.figure(figsize=(8, 8))
plt.pie(gender_percentage['客户数量'], labels=gender_percentage.index, autopct='%1.2f%%', startangle=90, textprops={'fontproperties': font_prop})
plt.title('客户性别分析', fontproperties=font_prop)
plt.axis('equal')

output_image_path_gender = os.path.join(output_folder, 'customer_gender_distribution.png')
plt.savefig(output_image_path_gender, dpi=300, bbox_inches='tight')
plt.close()

male_percentage = gender_percentage.loc['男', '客户数量'] if '男' in gender_percentage.index else 0
female_percentage = gender_percentage.loc['女', '客户数量'] if '女' in gender_percentage.index else 0
print(f"该网店男性客户占比是 {male_percentage:.2f}%，女性客户占比是 {female_percentage:.2f}%。")

output_excel_path_gender = os.path.join(output_folder, '客户性别分析.xlsx')
save_analysis_to_excel(
    gender_percentage,
    output_image_path_gender,
    output_excel_path_gender,
    '客户性别分析',
    f"该网店男性客户占比是 {male_percentage:.2f}%，女性客户占比是 {female_percentage:.2f}%。"
)

print("客户性别分析已完成，结果已保存到", output_excel_path_gender)